package com.validant.enigma3.common;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.testng.Assert;

/*
 * This class includes functions related to actions on Excel files
 */
public class SpreadSheet {

	// get data from test data file
	// only work with Excel (.xls) files
	public static Iterator<Object[]> getTestData(String xlsFile, String sheetName) {
		FileInputStream file = null;
		List<Object[]> retData = new ArrayList<Object[]>();

		try 
		{
	       // open Excel file
	       file = new FileInputStream(xlsFile);
	       
	       //Get the workbook instance for XLS file 
	       HSSFWorkbook workbook = new HSSFWorkbook(file);
	        
	       //Get first sheet from the workbook
	       HSSFSheet sheet = workbook.getSheet(sheetName);

	       //Get iterator to all the rows in current sheet
	       Iterator<Row> rowIterator = sheet.iterator();
	       
	       boolean isHeaderRow = true;
	       while ( rowIterator.hasNext() ) 
	       {
	           Row row = rowIterator.next();
	           
	           // ignore header row
	           if (isHeaderRow) {
	        	   isHeaderRow = false;
	        	   continue;
	           }
	            
	           //For each row, iterate through each columns
	           Iterator<Cell> cellIterator = row.cellIterator();
	           
	           List<Object> rowData = new ArrayList<Object>();
	           while ( cellIterator.hasNext() ) {
	               Cell cell = cellIterator.next();
	               rowData.add(cell.getStringCellValue());	               
	           }

	           retData.add(rowData.toArray());
	       }
	       
		   // close file
	       file.close();
	       
       } catch (FileNotFoundException e) {
    	   Assert.assertTrue(false, Constants.ERR_MSG_EXCEL_FILE_NOT_FOUND);
       } catch (IOException e) {
    	   Assert.assertTrue(false, e.getMessage());
       }
	
	   return retData.iterator();
	}
	
	public static Iterator<Object[]> getTestData(String xlsFile, int sheetIndex) {
		FileInputStream file = null;
		List<Object[]> retData = new ArrayList<Object[]>();

		try 
		{
	       // open Excel file
	       file = new FileInputStream(xlsFile);
	       
	       //Get the workbook instance for XLS file 
	       HSSFWorkbook workbook = new HSSFWorkbook(file);
	        
	       //Get first sheet from the workbook
	       HSSFSheet sheet = workbook.getSheetAt(sheetIndex);

	       //Get iterator to all the rows in current sheet
	       Iterator<Row> rowIterator = sheet.iterator();
	       
	       boolean isHeaderRow = true;
	       while ( rowIterator.hasNext() ) 
	       {
	           Row row = rowIterator.next();
	           
	           // ignore header row
	           if (isHeaderRow) {
	        	   isHeaderRow = false;
	        	   continue;
	           }
	            
	           //For each row, iterate through each columns
	           Iterator<Cell> cellIterator = row.cellIterator();
	           
	           List<Object> rowData = new ArrayList<Object>();
	           while ( cellIterator.hasNext() ) {
	               Cell cell = cellIterator.next();
	               rowData.add(cell.getStringCellValue());	               
	           }

	           retData.add(rowData.toArray());
	       }
	       
		   // close file
	       file.close();
	       
       } catch (FileNotFoundException e) {
    	   Assert.assertTrue(false, Constants.ERR_MSG_EXCEL_FILE_NOT_FOUND);
       } catch (IOException e) {
    	   Assert.assertTrue(false, e.getMessage());
       }
	
	   return retData.iterator();
	}
		
	// ADD MORE FUNCTIONS HERE!
}
